﻿//-----------------------------------------------------------------------
// <copyright file="SQLDbProvider.cs" >
//     All rights reserved.
//     THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY 
//     KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
//     IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
//     PARTICULAR PURPOSE.
// </copyright>
// <author>Marais van Zyl</author>
// <email>marais@projexis.co.za</email>
// <CreatedDate>2010/01/26</CreatedDate>
//-----------------------------------------------------------------------
namespace Accountability4net.MSSQLDbProvider
{
    using System;
    using System.Collections.Generic;
    using System.Collections.Specialized;
    using System.Data;
    using System.Data.SqlClient;
    using Accountability4net.Contact;
using Accountability4net.MSSQLDbProvider.Properties;

    /// <summary>
    /// Defines a SQLDbProvider to provide connectivity to use the platform using a SQL Server database.
    /// </summary>
    public class SQLDbProvider : MonengineProviderBase
    {
        private const string CONNECTION_STRING_KEY = "ConnectionString";
        private const string INSTALL_CONNECTION_STRING_KEY = "InstallConnectionString";
        private const string INSTALL_KEY = "Install";
        private string connectionString_;
        private string installConnectionString_;
        private SqlConnection connection_;
        
        public SQLDbProvider()
        {
        }
        
        /// <summary>
        /// The SQL Transaction used to execute commands against the database, if any.
        /// </summary>
        internal SqlTransaction Transaction
        {
            get;
            set;
        }

        /// <summary>
        /// Creates a SqlCommand object to use to execute commands against the database
        /// </summary>
        /// <param name="sqlString"></param>
        /// <returns></returns>
        internal SqlCommand CreateCommand(string sqlString, SqlConnection connection)
        {
            SqlCommand result = connection.CreateCommand();
            result.CommandText = sqlString;
            result.CommandType = System.Data.CommandType.Text;
            if (this.Transaction != null)
            {
                result.Transaction = this.Transaction;
            }

            return result;
        }

        /// <summary>
        /// Creates a SqlCommand object to use to execute commands against the database
        /// </summary>
        /// <param name="sqlString"></param>
        /// <returns></returns>
        internal SqlCommand CreateCommand(string sqlString)
        {
            return CreateCommand(sqlString, Connection);
        }

        /// <summary>
        /// Commits and Clears the SqlTransaction
        /// </summary>
        internal void CommitTransaction()
        {
            this.Transaction.Commit();
            this.Transaction = null;
        }

        public override void Initialize(string name, NameValueCollection config)
        {
            base.Initialize(name, config);
            connectionString_ = config[CONNECTION_STRING_KEY];
            var install = config[INSTALL_KEY];
            bool doInstall;
            if (Boolean.TryParse(install, out doInstall)
                && doInstall)
            {
                installConnectionString_ = config[INSTALL_CONNECTION_STRING_KEY];
                Install();
            }
        }

        public override void Install()
        {
            SqlConnection.ClearAllPools();
            using (var connection = new SqlConnection(installConnectionString_))
            {
                try
                {
                    connection.Open();
                    var sql = Resources.accountability4net_sql;
                    foreach (var queryPart in SplitIntoParts(sql))
                    {
                        using (var command = CreateCommand(queryPart, connection))
                        {
                            try
                            {
                                int rows = command.ExecuteNonQuery();
                            }
                            catch (Exception e)
                            {
                                Console.Error.WriteLine("Query: {0}\n failed with exception: {1}", queryPart, e);
                                //throw new Exception(queryPart, e);
                            }
                        }
                    }
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        private string[] SplitIntoParts(string query)
        {
            return query.Split(new string[] { "\r\nGO" }, StringSplitOptions.RemoveEmptyEntries);
        }

        private void ParseConnectionString(string connectionString, out string server, out string database, out string user, out string password)
        {
            server = null;
            database = null;
            user = null;
            password = null;
            string[] pairs = connectionString.Split(';');
            foreach (string pair in pairs)
            {
                string[] parts = pair.Split('=');
                if (parts.Length < 2)
                {
                    continue;
                }
                string key = parts[0];
                string value = parts[1];
                if (key.Equals("server", StringComparison.OrdinalIgnoreCase))
                {
                    server = value;
                }
                if (key.Equals("database", StringComparison.OrdinalIgnoreCase))
                {
                    database = value;
                }
                if (key.Equals("user", StringComparison.OrdinalIgnoreCase))
                {
                    user = value;
                }
                if (key.Equals("password", StringComparison.OrdinalIgnoreCase))
                {
                    password = value;
                }
            }
        }

        internal SqlConnection Connection
        {
            get
            {
                if (connection_ == null)
                {
                    connection_ = new SqlConnection(connectionString_);
                }
                if (connection_.State != ConnectionState.Open)
                {
                    connection_.Open();
                }
                return connection_;
            }
        }

        public override IAccount CreateAccount(int number, AccountType type)
        {
            return SQLDbAccount.Create(this, number, type);
        }

        public override IAccount GetAccount(int number)
        {
            return SQLDbAccount.Retrieve(this, number);
        }

        public override IList<IAccount> GetAccounts(int from, int limit)
        {
            return SQLDbAccount.Retrieve(this, from, limit);
        }

        public override IAction CreateAction(ActionType type, IAccount account, decimal amount)
        {
            return SQLDbAction.Create(this, type, account, amount);
        }

        public override ITransaction CreateTransaction(DateTimeOffset timeOffset)
        {
            return SQLDbTransaction.Create(this, timeOffset);
        }

        public override IList<ITransaction> GetTransactions()
        {
            return SQLDbTransaction.Retrieve(this);
        }

        public override IList<ITransaction> GetTransactions(IAccount account)
        {
            return SQLDbTransaction.Retrieve(this, account);
        }

        public override IList<ITransaction> GetTransactions(IAccount account, DateTimeOffset? after)
        {
            return SQLDbTransaction.Retrieve(this, account, after);
        }

        public override IList<ITransaction> GetTransactions(IAccount account, DateTimeOffset? after, DateTimeOffset? before)
        {
            return SQLDbTransaction.Retrieve(this, account, after, before);
        }

        public override void Dispose()
        {
            if (connection_ != null
                && connection_.State != ConnectionState.Closed)
            {
                try
                {
                    connection_.Close();
                }
                finally
                {
                    connection_.Dispose();
                    connection_ = null;
                }
            }
        }

        public string GetString(SqlDataReader reader, string column)
        {
            int field = reader.GetOrdinal(column);
            if (reader.IsDBNull(field))
            {
                return null;
            }
            else
            {
                return reader.GetString(field);
            }
        }

        public object GetDbString(string value)
        {
            if (string.IsNullOrEmpty(value))
            {
                return DBNull.Value;
            }

            return value;
        }

        public override IContact CreateContact()
        {
            return SQLDbContact.Create(this);
        }

        public override IContact GetContact(Guid id)
        {
            return SQLDbContact.Retrieve(this, id);
        }
    }
}
